OpenEdge Development:<br />Progress 4GL Handbook GET statements

You use a form of the GET statement to change the current position within the record set that the OPEN QUERY statement defines. You’ve already seen these statements on the button triggers in the h-CustOrderWin1.w procedure you built in Chapter 4, " Introducing the OpenEdge AppBuilder." This is the syntax for the GET statement:


GET [ FIRST \| NEXT \| PREV \| LAST \| CURRENT ] `query-name`.

The query must be open before you can use a GET statement. If the query involves a join, Progress populates all the buffers used in the query on each GET statement. As noted earlier, a record can remain current through multiple GET statements if a second table in the query has multiple records matching the record for the first table. This would be the case for a Customer and its Orders, for example. A series of GET NEXT statements leaves the same Customer record in its buffer as long as there is another matching Order record to read into the Order buffer.

When you first open a query, it is positioned in effect before the first record in the result set. Either a GET FIRST or a GET NEXT statement positions to the first record in the result set.

If you execute a GET NEXT statement when the query is already positioned on the last record, then the query is positioned effectively beyond the end of the result set. A GET PREV statement then repositions to the last record in the result set. Likewise, a GET PREV statement executed when already on the first record results in the query being positioned before the first record, and a GET FIRST or GET NEXT statement repositions to the first record. When the query is repositioned off the beginning or off the end of the result set, no error results. You can use the AVAILABLE function that you’re already familiar with to check whether you have positioned beyond the result set. For example, this code opens a query and cycles through all the records in its result set, simply counting them as it goes:

DEFINE QUERY CustOrd FOR Customer, Order. DEFINE VARIABLE iCount AS INTEGER NO-UNDO. OPEN QUERY CustOrd FOR EACH Customer, EACH Order OF Customer. GET FIRST CustOrd. DO WHILE AVAILABLE(Customer): iCount = iCount + 1. GET NEXT CustOrd. END. DISPLAY iCount.

DEFINE QUERY CustOrd FOR Customer, Order. 
DEFINE VARIABLE iCount AS INTEGER     NO-UNDO. 
    OPEN QUERY CustOrd FOR EACH Customer, EACH Order OF Customer. 
    GET FIRST CustOrd. 
    DO WHILE AVAILABLE(Customer): 
        iCount = iCount + 1. 
        GET NEXT CustOrd. 
    END. 
    DISPLAY iCount.

The GET FIRST statement is needed to make a record available before the IF AVAILABLE statement is first encountered. Otherwise, the AVAILABLE test would fail before the code ever entered the loop.

Note also that the AVAILABLE function must take a buffer name as its argument, not the name of the query. If the query involves an outer join, then you should be careful about which buffer you use in the AVAILABLE function. If you name a buffer that could be empty because of an outer join (such as an empty Order buffer for a Customer with no Orders), then your loop could terminate prematurely. On the other hand, you might want your application logic to test specifically for the presence of one buffer or another in order to take special action when one of the buffers has no record.

Using the QUERY-OFF-END function

There is a built-in Progress function that you can use for the same purpose as the AVAILABLE statement:


QUERY-OFF-END ( `query-name` ).

QUERY-OFF-END is a logical function that returns true if the query is positioned either before the first result set row or after the last row, and false if it is positioned directly on any row in the result set. The query-name parameter must be either a quoted literal string with the name of the query or a variable name that has been set to the name of the query. In this way, you can use the statement programmatically to test potentially multiple different active queries in your procedure.

For example, here is the same procedure used above, this time with the QUERY-OFF-END function in place of AVAILABLE:

DEFINE QUERY CustOrd FOR Customer, Order. DEFINE VARIABLE iCount AS INTEGER NO-UNDO. OPEN QUERY CustOrd FOR EACH Customer, EACH Order OF Customer. GET FIRST CustOrd. DO WHILE `NOT QUERY-OFF-END('CustOrd')`: iCount = iCount + 1. GET NEXT CustOrd. END. DISPLAY iCount.

DEFINE QUERY CustOrd FOR Customer, Order. 
DEFINE VARIABLE iCount AS INTEGER     NO-UNDO. 
    OPEN QUERY CustOrd FOR EACH Customer, EACH Order OF Customer. 
    GET FIRST CustOrd. 
    DO WHILE NOT QUERY-OFF-END('CustOrd'): 
        iCount = iCount + 1. 
        GET NEXT CustOrd. 
    END. 
    DISPLAY iCount.

The difference between QUERY-OFF-END and AVAILABLE is simply that AVAILABLE requires a buffer name as a parameter, whereas QUERY-OFF-END requires a query name. If you use the AVAILABLE function with the name of the first buffer in the query, it is equivalent to using QUERY-OFF-END with the query name. Just for stylistic reasons, it is more appropriate to use the QUERY-OFF-END function in most cases, since it is the position of the query and not the presence of a record in a particular buffer that you’re really interested in. By contrast, if you really want to test for the presence of a record, especially when your query does an outer join that might not always retrieve a record into every buffer, then use the AVAILABLE function.